{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 该类型特征的 column name 都以 B_ 开头，意为Brand（品牌）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from pandas.tseries.offsets import *\n",
    "from xiao_utils import f"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 20297 entries, 0 to 139\n",
      "Data columns (total 32 columns):\n",
      "TR                       20157 non-null object\n",
      "brand_id                 20297 non-null int64\n",
      "car_height               20157 non-null float64\n",
      "car_length               20157 non-null float64\n",
      "car_width                20157 non-null float64\n",
      "class_id                 20297 non-null int64\n",
      "compartment              20157 non-null float64\n",
      "cylinder_number          20157 non-null float64\n",
      "department_id            20157 non-null float64\n",
      "displacement             20157 non-null float64\n",
      "driven_type_id           20157 non-null float64\n",
      "emission_standards_id    20157 non-null float64\n",
      "engine_torque            20138 non-null float64\n",
      "equipment_quality        20157 non-null float64\n",
      "front_track              20157 non-null float64\n",
      "fuel_type_id             20154 non-null float64\n",
      "gearbox_type             20157 non-null object\n",
      "if_MPV_id                20157 non-null float64\n",
      "if_charging              20157 non-null object\n",
      "if_luxurious_id          20157 non-null float64\n",
      "level_id                 19859 non-null float64\n",
      "newenergy_type_id        20157 non-null float64\n",
      "power                    20157 non-null float64\n",
      "price                    11377 non-null float64\n",
      "price_level              20157 non-null float64\n",
      "rated_passenger          20157 non-null object\n",
      "rear_track               20157 non-null float64\n",
      "sale_date                20297 non-null datetime64[ns]\n",
      "sale_quantity            20157 non-null float64\n",
      "total_quality            20157 non-null float64\n",
      "type_id                  20157 non-null float64\n",
      "wheelbase                20157 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(25), int64(2), object(4)\n",
      "memory usage: 5.1+ MB\n"
     ]
    }
   ],
   "source": [
    "# 将level_id字段中的-替换为np.nan\n",
    "df = pd.read_csv('../../data/origin/[new] yancheng_train_20171226.csv', dtype={'sale_date':str}, na_values=['-'], low_memory=False)\n",
    "df['sale_date']= pd.to_datetime(df['sale_date'], format='%Y%m')\n",
    "\n",
    "# 将price_level字段转换成有序类别的类型，并用其数值填入该列。\n",
    "df['price_level'] = df['price_level'].astype('category', categories=['5WL','5-8W','8-10W','10-15W','15-20W','20-25W','25-35W','35-50W','50-75W'], ordered=True)\n",
    "df['price_level'] = df['price_level'].cat.codes\n",
    "\n",
    "# 待选方案：先把power和扭矩字段带/的行复制一份，然后将新行里的销量清零，将原行和新行的power和扭矩字段的值分别赋为slash前后的值。\n",
    "# 现行方案：先他娘的直接把slash和后面的值删掉。省得影响记录条数相关的统计量。\n",
    "def process_power_and_torque(s):\n",
    "    return s.split('/')[0]\n",
    "df['power'] = df['power'].astype(str).apply(process_power_and_torque).astype(float) #[18600]\n",
    "df['engine_torque'] = df['engine_torque'].astype(str).apply(process_power_and_torque).astype(float)\n",
    "\n",
    "# -------------------------------------------------------------\n",
    "# 把2017年11月的数据拼进来，一块填入其特征，用于最终输出要提交的结果。\n",
    "empty_Nov = pd.read_csv('../../data/origin/yancheng_testA_20171225.csv', dtype={'predict_date':str}, na_values=['-'], low_memory=False)\n",
    "empty_Nov['predict_date']= pd.to_datetime(empty_Nov['predict_date'], format='%Y%m')\n",
    "empty_Nov.rename(columns = {'predict_date': 'sale_date', 'predict_quantity':'sale_quantity'}, inplace = True)\n",
    "\n",
    "\n",
    "# 读取玩了，先不急着拼，先把车型到品牌的映射关系join进来\n",
    "class_to_brand = df[['class_id','brand_id']].groupby(['class_id']).mean().reset_index()\n",
    "empyt_Nov = pd.merge(left=empty_Nov, right=class_to_brand, on='class_id', how='left')\n",
    "empty_Nov['brand_id']= class_to_brand['brand_id']\n",
    "# empty_Nov\n",
    "# class_to_brand\n",
    "\n",
    "# class_to_brand\n",
    "\n",
    "# 读取完了，拼上去\n",
    "df = pd.concat([df, empty_Nov])\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "tt = df.groupby(['brand_id','sale_date']).sum().reset_index()[['brand_id','sale_date','sale_quantity']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "%qtconsole"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 主要逻辑\n",
    "def calc_features_on_brand_and_time(df):\n",
    "    \"\"\"\n",
    "    Args:\n",
    "        df: 完整的数据集\n",
    "    Return:\n",
    "        tmp：基于综合时间和品牌信息，构造出的特征们\n",
    "    \"\"\"\n",
    "    g_cls_date = df.groupby(['brand_id','sale_date']).sum().reset_index()[['brand_id','sale_date','sale_quantity']]\n",
    "    gg = g_cls_date.groupby('brand_id')\n",
    "\n",
    "    # 过去几年内的每个月销量\n",
    "    tmp = g_cls_date\n",
    "    for i in range(62):\n",
    "        tmp['B_som_' + str(i+1)] = gg.apply(f, -(i+1), -i).reset_index()['sale_date']\n",
    "\n",
    "\n",
    "    # 该车型过去2~60个月分别的销量和\n",
    "    tmp['B_ssm_1'] = tmp['B_som_1']\n",
    "    for i in range(60):\n",
    "        tmp['B_ssm_' + str(i+2) ] = tmp['B_ssm_' + str(i+1)] + tmp['B_som_' + str(i+2)]\n",
    "    tmp = tmp.drop('B_ssm_1', axis=1) # 再把这一列删掉，因为和前面的 B_som_1 列是重复的\n",
    "    \n",
    "    # 一阶差分，一阶比值\n",
    "    for i in range(61):\n",
    "        thismonth = tmp['B_som_' + str(i+1)]\n",
    "        lastmonth = tmp['B_som_' + str(i+2)]\n",
    "        tmp['B_fd_' + str(i+1)] = thismonth - lastmonth\n",
    "        tmp['B_fr_' + str(i+1)] = thismonth / lastmonth\n",
    "    \n",
    "    # 二阶差分\n",
    "    for i in range(60):\n",
    "        thismonth = tmp['B_fd_' + str(i+1)]\n",
    "        lastmonth = tmp['B_fd_' + str(i+2)]\n",
    "        tmp['B_sd_' + str(i+1)] = thismonth - lastmonth\n",
    "        \n",
    "    # 二阶比值\n",
    "    for i in range(60):\n",
    "        thismonth = tmp['B_fr_' + str(i+1)]\n",
    "        lastmonth = tmp['B_fr_' + str(i+2)]\n",
    "        tmp['B_sr_' + str(i+1)] = thismonth / lastmonth\n",
    "        \n",
    "    # 比值的差分\n",
    "    for i in range(60):\n",
    "        thismonth = tmp['B_fr_' + str(i+1)]\n",
    "        lastmonth = tmp['B_fr_' + str(i+2)]\n",
    "        tmp['B_dfr_' + str(i+1)] = thismonth - lastmonth\n",
    "    \n",
    "    # 差分的比值\n",
    "    for i in range(60):\n",
    "        thismonth = tmp['B_fd_' + str(i+1)]\n",
    "        lastmonth = tmp['B_fd_' + str(i+2)]\n",
    "        tmp['B_rfd_' + str(i+1)] = thismonth / lastmonth\n",
    "        \n",
    "        \n",
    "    # 相邻年，一阶差分，一阶比值\n",
    "    for i in range(4):\n",
    "        thismonth = tmp['B_som_' + str((i+1)*12)] # last year\n",
    "        lastmonth = tmp['B_som_' + str((i+2)*12)]\n",
    "        tmp['B_fdy_' + str(i+1)] = thismonth - lastmonth\n",
    "        tmp['B_fry_' + str(i+1)] = thismonth / lastmonth\n",
    "    \n",
    "    # 相邻年，二阶差分\n",
    "    for i in range(3):\n",
    "        thismonth = tmp['B_fdy_' + str(i+1)]\n",
    "        lastmonth = tmp['B_fdy_' + str(i+2)]\n",
    "        tmp['B_sdy_' + str(i+1)] = thismonth - lastmonth\n",
    "        \n",
    "    # 相邻年，二阶比值\n",
    "    for i in range(3):\n",
    "        thismonth = tmp['B_fry_' + str(i+1)]\n",
    "        lastmonth = tmp['B_fry_' + str(i+2)]\n",
    "        tmp['B_sry_' + str(i+1)] = thismonth / lastmonth\n",
    "        \n",
    "    # 相邻年，比值的差分\n",
    "    for i in range(3):\n",
    "        thismonth = tmp['B_fry_' + str(i+1)]\n",
    "        lastmonth = tmp['B_fry_' + str(i+2)]\n",
    "        tmp['B_dfry_' + str(i+1)] = thismonth - lastmonth\n",
    "    \n",
    "    # 相邻年，差分的比值\n",
    "    for i in range(3):\n",
    "        thismonth = tmp['B_fdy_' + str(i+1)]\n",
    "        lastmonth = tmp['B_fdy_' + str(i+2)]\n",
    "        tmp['B_rfdy_' + str(i+1)] = thismonth / lastmonth\n",
    "    \n",
    "#     #================================================================\n",
    "#     # 下面准备一下部分品牌的历史各月销量\n",
    "#     g_date = df[['sale_date','sale_quantity']].groupby('sale_date').sum()\n",
    "#     g_date = g_date.rename(columns={'sale_quantity':'T_som_0'})\n",
    "    \n",
    "#     ratios = pd.merge(g_cls_date, g_date, how='left', left_on='sale_date', right_index=True)\n",
    "#     ratios['B_rcm_0'] = ratios['sale_quantity'] / ratios['T_som_0']\n",
    "    \n",
    "#     gg = ratios.groupby('brand_id')\n",
    "    \n",
    "#     tmp['B_rcm_0'] = ratios['B_rcm_0']\n",
    "#     # 过去几年内的每个月车型销量占比\n",
    "#     for i in range(62):\n",
    "#         tmp['B_rcm_' + str(i+1)] = gg.apply(f, -(i+1), -i, 'B_rcm_0').reset_index()['sale_date']\n",
    "# #         tmp['B_rcm_' + str(i+1)] = tmp['sale_date'].apply(lambda x: g_date[x])\n",
    "#     tmp = tmp.drop('B_rcm_0', axis=1)\n",
    "    \n",
    "#     # 一阶差分，一阶比值\n",
    "#     for i in range(61):\n",
    "#         thismonth = tmp['B_rcm_' + str(i+1)]\n",
    "#         lastmonth = tmp['B_rcm_' + str(i+2)]\n",
    "#         tmp['B_rcm_fd_' + str(i+1)] = thismonth - lastmonth\n",
    "#         tmp['B_rcm_fr_' + str(i+1)] = thismonth / lastmonth\n",
    "    \n",
    "#     # 二阶差分\n",
    "#     for i in range(60):\n",
    "#         thismonth = tmp['B_rcm_fd_' + str(i+1)]\n",
    "#         lastmonth = tmp['B_rcm_fd_' + str(i+2)]\n",
    "#         tmp['B_rcm_sd_' + str(i+1)] = thismonth - lastmonth\n",
    "        \n",
    "#     # 二阶比值\n",
    "#     for i in range(60):\n",
    "#         thismonth = tmp['B_rcm_fr_' + str(i+1)]\n",
    "#         lastmonth = tmp['B_rcm_fr_' + str(i+2)]\n",
    "#         tmp['B_rcm_sr_' + str(i+1)] = thismonth / lastmonth\n",
    "        \n",
    "#     # 比值的差分\n",
    "#     for i in range(60):\n",
    "#         thismonth = tmp['B_rcm_fr_' + str(i+1)]\n",
    "#         lastmonth = tmp['B_rcm_fr_' + str(i+2)]\n",
    "#         tmp['B_rcm_dfr_' + str(i+1)] = thismonth - lastmonth\n",
    "    \n",
    "#     # 差分的比值\n",
    "#     for i in range(60):\n",
    "#         thismonth = tmp['B_rcm_fd_' + str(i+1)]\n",
    "#         lastmonth = tmp['B_rcm_fd_' + str(i+2)]\n",
    "#         tmp['B_rcm_rfd_' + str(i+1)] = thismonth / lastmonth\n",
    "\n",
    "\n",
    "#     # 相邻年，一阶差分，一阶比值\n",
    "#     for i in range(4):\n",
    "#         thismonth = tmp['B_rcm_' + str((i+1)*12)]\n",
    "#         lastmonth = tmp['B_rcm_' + str((i+2)*12)]\n",
    "#         tmp['B_rcm_fdy_' + str(i+1)] = thismonth - lastmonth\n",
    "#         tmp['B_rcm_fry_' + str(i+1)] = thismonth / lastmonth\n",
    "    \n",
    "#     # 相邻年，二阶差分\n",
    "#     for i in range(3):\n",
    "#         thismonth = tmp['B_rcm_fdy_' + str(i+1)]\n",
    "#         lastmonth = tmp['B_rcm_fdy_' + str(i+2)]\n",
    "#         tmp['B_rcm_sdy_' + str(i+1)] = thismonth - lastmonth\n",
    "        \n",
    "#     # 相邻年，二阶比值\n",
    "#     for i in range(3):\n",
    "#         thismonth = tmp['B_rcm_fry_' + str(i+1)]\n",
    "#         lastmonth = tmp['B_rcm_fry_' + str(i+2)]\n",
    "#         tmp['B_rcm_sry_' + str(i+1)] = thismonth / lastmonth\n",
    "        \n",
    "#     # 相邻年，比值的差分\n",
    "#     for i in range(3):\n",
    "#         thismonth = tmp['B_rcm_fry_' + str(i+1)]\n",
    "#         lastmonth = tmp['B_rcm_fry_' + str(i+2)]\n",
    "#         tmp['B_rcm_dfry_' + str(i+1)] = thismonth - lastmonth\n",
    "    \n",
    "#     # 相邻年，差分的比值\n",
    "#     for i in range(3):\n",
    "#         thismonth = tmp['B_rcm_fdy_' + str(i+1)]\n",
    "#         lastmonth = tmp['B_rcm_fdy_' + str(i+2)]\n",
    "#         tmp['B_rcm_rfdy_' + str(i+1)] = thismonth / lastmonth\n",
    "    \n",
    "    # 注意要把np.inf替换为空值，在上面算月销量比例时，引入了inf，其实应该作为空值。\n",
    "    # 注意过程中产生的 0 也要都换成空值！因为实际上不可能有有意义的0出现的。\n",
    "    return tmp.replace([np.inf, -np.inf, 0], np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp1 = calc_features_on_brand_and_time(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>brand_id</th>\n",
       "      <th>sale_date</th>\n",
       "      <th>sale_quantity</th>\n",
       "      <th>B_som_1</th>\n",
       "      <th>B_som_2</th>\n",
       "      <th>B_som_3</th>\n",
       "      <th>B_som_4</th>\n",
       "      <th>B_som_5</th>\n",
       "      <th>B_som_6</th>\n",
       "      <th>B_som_7</th>\n",
       "      <th>...</th>\n",
       "      <th>B_sdy_3</th>\n",
       "      <th>B_sry_1</th>\n",
       "      <th>B_sry_2</th>\n",
       "      <th>B_sry_3</th>\n",
       "      <th>B_dfry_1</th>\n",
       "      <th>B_dfry_2</th>\n",
       "      <th>B_dfry_3</th>\n",
       "      <th>B_rfdy_1</th>\n",
       "      <th>B_rfdy_2</th>\n",
       "      <th>B_rfdy_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-01-01</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-02-01</td>\n",
       "      <td>21.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-03-01</td>\n",
       "      <td>10.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-04-01</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-05-01</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-10-01</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-11-01</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-12-01</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>12</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>31.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>12</td>\n",
       "      <td>2013-03-01</td>\n",
       "      <td>26.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>2013-04-01</td>\n",
       "      <td>10.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>2013-05-01</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>12</td>\n",
       "      <td>2013-06-01</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>12</td>\n",
       "      <td>2013-09-01</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>12</td>\n",
       "      <td>2013-11-01</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>12</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.340426</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>12</td>\n",
       "      <td>2014-02-01</td>\n",
       "      <td>10.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>12</td>\n",
       "      <td>2014-08-01</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>12</td>\n",
       "      <td>2014-09-01</td>\n",
       "      <td>158.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>12</td>\n",
       "      <td>2014-10-01</td>\n",
       "      <td>437.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>12</td>\n",
       "      <td>2014-11-01</td>\n",
       "      <td>447.0</td>\n",
       "      <td>437.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.500000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>12</td>\n",
       "      <td>2014-12-01</td>\n",
       "      <td>455.0</td>\n",
       "      <td>447.0</td>\n",
       "      <td>437.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>12</td>\n",
       "      <td>2015-01-01</td>\n",
       "      <td>857.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>447.0</td>\n",
       "      <td>437.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>47.0</td>\n",
       "      <td>0.782518</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.143445</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.937500</td>\n",
       "      <td>-0.340426</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>12</td>\n",
       "      <td>2015-02-01</td>\n",
       "      <td>846.0</td>\n",
       "      <td>857.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>447.0</td>\n",
       "      <td>437.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.476190</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>12</td>\n",
       "      <td>2015-03-01</td>\n",
       "      <td>455.0</td>\n",
       "      <td>846.0</td>\n",
       "      <td>857.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>447.0</td>\n",
       "      <td>437.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.600000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.625000</td>\n",
       "      <td>1.600000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>12</td>\n",
       "      <td>2015-04-01</td>\n",
       "      <td>296.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>846.0</td>\n",
       "      <td>857.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>447.0</td>\n",
       "      <td>437.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>12</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>522.0</td>\n",
       "      <td>296.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>846.0</td>\n",
       "      <td>857.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>447.0</td>\n",
       "      <td>437.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.500000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.500000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>12</td>\n",
       "      <td>2015-06-01</td>\n",
       "      <td>419.0</td>\n",
       "      <td>522.0</td>\n",
       "      <td>296.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>846.0</td>\n",
       "      <td>857.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>447.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>12</td>\n",
       "      <td>2015-07-01</td>\n",
       "      <td>506.0</td>\n",
       "      <td>419.0</td>\n",
       "      <td>522.0</td>\n",
       "      <td>296.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>846.0</td>\n",
       "      <td>857.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>...</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1703</th>\n",
       "      <td>985</td>\n",
       "      <td>2015-06-01</td>\n",
       "      <td>322.0</td>\n",
       "      <td>542.0</td>\n",
       "      <td>512.0</td>\n",
       "      <td>408.0</td>\n",
       "      <td>413.0</td>\n",
       "      <td>698.0</td>\n",
       "      <td>409.0</td>\n",
       "      <td>382.0</td>\n",
       "      <td>...</td>\n",
       "      <td>241.0</td>\n",
       "      <td>1.110129</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.111043</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.500000</td>\n",
       "      <td>0.008299</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1704</th>\n",
       "      <td>985</td>\n",
       "      <td>2015-07-01</td>\n",
       "      <td>370.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>542.0</td>\n",
       "      <td>512.0</td>\n",
       "      <td>408.0</td>\n",
       "      <td>413.0</td>\n",
       "      <td>698.0</td>\n",
       "      <td>409.0</td>\n",
       "      <td>...</td>\n",
       "      <td>295.0</td>\n",
       "      <td>0.882556</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.129387</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.300000</td>\n",
       "      <td>0.101695</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705</th>\n",
       "      <td>985</td>\n",
       "      <td>2015-08-01</td>\n",
       "      <td>323.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>542.0</td>\n",
       "      <td>512.0</td>\n",
       "      <td>408.0</td>\n",
       "      <td>413.0</td>\n",
       "      <td>698.0</td>\n",
       "      <td>...</td>\n",
       "      <td>371.0</td>\n",
       "      <td>0.810763</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.207600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.250000</td>\n",
       "      <td>0.097035</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1706</th>\n",
       "      <td>985</td>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>467.0</td>\n",
       "      <td>323.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>542.0</td>\n",
       "      <td>512.0</td>\n",
       "      <td>408.0</td>\n",
       "      <td>413.0</td>\n",
       "      <td>...</td>\n",
       "      <td>332.0</td>\n",
       "      <td>1.739638</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.657208</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-4.351351</td>\n",
       "      <td>-0.111446</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1707</th>\n",
       "      <td>985</td>\n",
       "      <td>2015-10-01</td>\n",
       "      <td>469.0</td>\n",
       "      <td>467.0</td>\n",
       "      <td>323.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>542.0</td>\n",
       "      <td>512.0</td>\n",
       "      <td>408.0</td>\n",
       "      <td>...</td>\n",
       "      <td>419.0</td>\n",
       "      <td>1.160122</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.150950</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.541667</td>\n",
       "      <td>-0.057279</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1708</th>\n",
       "      <td>985</td>\n",
       "      <td>2015-11-01</td>\n",
       "      <td>425.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>467.0</td>\n",
       "      <td>323.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>542.0</td>\n",
       "      <td>512.0</td>\n",
       "      <td>...</td>\n",
       "      <td>354.0</td>\n",
       "      <td>1.472927</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.404794</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.549020</td>\n",
       "      <td>-0.144068</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1709</th>\n",
       "      <td>985</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>469.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>467.0</td>\n",
       "      <td>323.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>542.0</td>\n",
       "      <td>...</td>\n",
       "      <td>313.0</td>\n",
       "      <td>0.708745</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.395475</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.142857</td>\n",
       "      <td>0.357827</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1710</th>\n",
       "      <td>985</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>553.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>467.0</td>\n",
       "      <td>323.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-473.0</td>\n",
       "      <td>1.105795</td>\n",
       "      <td>0.845293</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.103857</td>\n",
       "      <td>-0.179668</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-4.583333</td>\n",
       "      <td>-0.131868</td>\n",
       "      <td>0.161348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1711</th>\n",
       "      <td>985</td>\n",
       "      <td>2016-02-01</td>\n",
       "      <td>248.0</td>\n",
       "      <td>553.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>467.0</td>\n",
       "      <td>323.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-199.0</td>\n",
       "      <td>2.276140</td>\n",
       "      <td>0.476013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.829937</td>\n",
       "      <td>-0.715892</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.893333</td>\n",
       "      <td>-1.304348</td>\n",
       "      <td>0.366242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1712</th>\n",
       "      <td>985</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>319.0</td>\n",
       "      <td>248.0</td>\n",
       "      <td>553.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>467.0</td>\n",
       "      <td>323.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-352.0</td>\n",
       "      <td>3.312719</td>\n",
       "      <td>0.790449</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.573693</td>\n",
       "      <td>-0.180390</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.670588</td>\n",
       "      <td>1.976744</td>\n",
       "      <td>-0.139159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1713</th>\n",
       "      <td>985</td>\n",
       "      <td>2016-04-01</td>\n",
       "      <td>535.0</td>\n",
       "      <td>319.0</td>\n",
       "      <td>248.0</td>\n",
       "      <td>553.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>467.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-68.0</td>\n",
       "      <td>1.056298</td>\n",
       "      <td>0.861243</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.076869</td>\n",
       "      <td>-0.219981</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.652632</td>\n",
       "      <td>0.989583</td>\n",
       "      <td>0.585366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1714</th>\n",
       "      <td>985</td>\n",
       "      <td>2016-05-01</td>\n",
       "      <td>341.0</td>\n",
       "      <td>535.0</td>\n",
       "      <td>319.0</td>\n",
       "      <td>248.0</td>\n",
       "      <td>553.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>...</td>\n",
       "      <td>34.0</td>\n",
       "      <td>2.001847</td>\n",
       "      <td>0.364680</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.793129</td>\n",
       "      <td>-1.379188</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.222222</td>\n",
       "      <td>-0.386266</td>\n",
       "      <td>1.170854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1715</th>\n",
       "      <td>985</td>\n",
       "      <td>2016-06-01</td>\n",
       "      <td>281.0</td>\n",
       "      <td>341.0</td>\n",
       "      <td>535.0</td>\n",
       "      <td>319.0</td>\n",
       "      <td>248.0</td>\n",
       "      <td>553.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-239.0</td>\n",
       "      <td>1.057607</td>\n",
       "      <td>1.110129</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.064482</td>\n",
       "      <td>0.111043</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.724138</td>\n",
       "      <td>14.500000</td>\n",
       "      <td>0.008299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1716</th>\n",
       "      <td>985</td>\n",
       "      <td>2016-07-01</td>\n",
       "      <td>279.0</td>\n",
       "      <td>281.0</td>\n",
       "      <td>341.0</td>\n",
       "      <td>535.0</td>\n",
       "      <td>319.0</td>\n",
       "      <td>248.0</td>\n",
       "      <td>553.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-265.0</td>\n",
       "      <td>1.204234</td>\n",
       "      <td>0.882556</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.198578</td>\n",
       "      <td>-0.129387</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-6.000000</td>\n",
       "      <td>-0.300000</td>\n",
       "      <td>0.101695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1717</th>\n",
       "      <td>985</td>\n",
       "      <td>2016-08-01</td>\n",
       "      <td>382.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>281.0</td>\n",
       "      <td>341.0</td>\n",
       "      <td>535.0</td>\n",
       "      <td>319.0</td>\n",
       "      <td>248.0</td>\n",
       "      <td>553.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-335.0</td>\n",
       "      <td>1.003182</td>\n",
       "      <td>0.810763</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.002830</td>\n",
       "      <td>-0.207600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>-1.250000</td>\n",
       "      <td>0.097035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1718</th>\n",
       "      <td>985</td>\n",
       "      <td>2016-09-01</td>\n",
       "      <td>416.0</td>\n",
       "      <td>382.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>281.0</td>\n",
       "      <td>341.0</td>\n",
       "      <td>535.0</td>\n",
       "      <td>319.0</td>\n",
       "      <td>248.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-369.0</td>\n",
       "      <td>0.662536</td>\n",
       "      <td>1.739638</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.521640</td>\n",
       "      <td>0.657208</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.068323</td>\n",
       "      <td>-4.351351</td>\n",
       "      <td>-0.111446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1719</th>\n",
       "      <td>985</td>\n",
       "      <td>2016-10-01</td>\n",
       "      <td>521.0</td>\n",
       "      <td>416.0</td>\n",
       "      <td>382.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>281.0</td>\n",
       "      <td>341.0</td>\n",
       "      <td>535.0</td>\n",
       "      <td>319.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-443.0</td>\n",
       "      <td>0.992664</td>\n",
       "      <td>1.160122</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.008023</td>\n",
       "      <td>0.150950</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-1.541667</td>\n",
       "      <td>-0.057279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1720</th>\n",
       "      <td>985</td>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>490.0</td>\n",
       "      <td>521.0</td>\n",
       "      <td>416.0</td>\n",
       "      <td>382.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>281.0</td>\n",
       "      <td>341.0</td>\n",
       "      <td>535.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-405.0</td>\n",
       "      <td>0.882480</td>\n",
       "      <td>1.472927</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.148161</td>\n",
       "      <td>0.404794</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.544304</td>\n",
       "      <td>-1.549020</td>\n",
       "      <td>-0.144068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1721</th>\n",
       "      <td>985</td>\n",
       "      <td>2016-12-01</td>\n",
       "      <td>845.0</td>\n",
       "      <td>490.0</td>\n",
       "      <td>521.0</td>\n",
       "      <td>416.0</td>\n",
       "      <td>382.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>281.0</td>\n",
       "      <td>341.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-201.0</td>\n",
       "      <td>1.191558</td>\n",
       "      <td>0.708745</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.184346</td>\n",
       "      <td>-0.395475</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-3.750000</td>\n",
       "      <td>-0.142857</td>\n",
       "      <td>0.357827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1722</th>\n",
       "      <td>985</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>326.0</td>\n",
       "      <td>845.0</td>\n",
       "      <td>490.0</td>\n",
       "      <td>521.0</td>\n",
       "      <td>416.0</td>\n",
       "      <td>382.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>281.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-103.0</td>\n",
       "      <td>0.729836</td>\n",
       "      <td>1.105795</td>\n",
       "      <td>0.845293</td>\n",
       "      <td>-0.293273</td>\n",
       "      <td>0.103857</td>\n",
       "      <td>-0.179668</td>\n",
       "      <td>-2.636364</td>\n",
       "      <td>-4.583333</td>\n",
       "      <td>-0.131868</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1723</th>\n",
       "      <td>985</td>\n",
       "      <td>2017-02-01</td>\n",
       "      <td>353.0</td>\n",
       "      <td>326.0</td>\n",
       "      <td>845.0</td>\n",
       "      <td>490.0</td>\n",
       "      <td>521.0</td>\n",
       "      <td>416.0</td>\n",
       "      <td>382.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-265.0</td>\n",
       "      <td>0.405654</td>\n",
       "      <td>2.276140</td>\n",
       "      <td>0.476013</td>\n",
       "      <td>-0.879802</td>\n",
       "      <td>0.829937</td>\n",
       "      <td>-0.715892</td>\n",
       "      <td>-1.231343</td>\n",
       "      <td>-0.893333</td>\n",
       "      <td>-1.304348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1724</th>\n",
       "      <td>985</td>\n",
       "      <td>2017-03-01</td>\n",
       "      <td>387.0</td>\n",
       "      <td>353.0</td>\n",
       "      <td>326.0</td>\n",
       "      <td>845.0</td>\n",
       "      <td>490.0</td>\n",
       "      <td>521.0</td>\n",
       "      <td>416.0</td>\n",
       "      <td>382.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-42.0</td>\n",
       "      <td>0.346856</td>\n",
       "      <td>3.312719</td>\n",
       "      <td>0.790449</td>\n",
       "      <td>-1.472281</td>\n",
       "      <td>1.573693</td>\n",
       "      <td>-0.180390</td>\n",
       "      <td>-0.392070</td>\n",
       "      <td>-2.670588</td>\n",
       "      <td>1.976744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1725</th>\n",
       "      <td>985</td>\n",
       "      <td>2017-04-01</td>\n",
       "      <td>371.0</td>\n",
       "      <td>387.0</td>\n",
       "      <td>353.0</td>\n",
       "      <td>326.0</td>\n",
       "      <td>845.0</td>\n",
       "      <td>490.0</td>\n",
       "      <td>521.0</td>\n",
       "      <td>416.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.724506</td>\n",
       "      <td>1.056298</td>\n",
       "      <td>0.861243</td>\n",
       "      <td>-0.397332</td>\n",
       "      <td>0.076869</td>\n",
       "      <td>-0.219981</td>\n",
       "      <td>0.146497</td>\n",
       "      <td>1.652632</td>\n",
       "      <td>0.989583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1726</th>\n",
       "      <td>985</td>\n",
       "      <td>2017-05-01</td>\n",
       "      <td>404.0</td>\n",
       "      <td>371.0</td>\n",
       "      <td>387.0</td>\n",
       "      <td>353.0</td>\n",
       "      <td>326.0</td>\n",
       "      <td>845.0</td>\n",
       "      <td>490.0</td>\n",
       "      <td>521.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-323.0</td>\n",
       "      <td>0.396992</td>\n",
       "      <td>2.001847</td>\n",
       "      <td>0.364680</td>\n",
       "      <td>-0.955644</td>\n",
       "      <td>0.793129</td>\n",
       "      <td>-1.379188</td>\n",
       "      <td>-1.005000</td>\n",
       "      <td>-2.222222</td>\n",
       "      <td>-0.386266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1727</th>\n",
       "      <td>985</td>\n",
       "      <td>2017-06-01</td>\n",
       "      <td>273.0</td>\n",
       "      <td>404.0</td>\n",
       "      <td>371.0</td>\n",
       "      <td>387.0</td>\n",
       "      <td>353.0</td>\n",
       "      <td>326.0</td>\n",
       "      <td>845.0</td>\n",
       "      <td>490.0</td>\n",
       "      <td>...</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0.737163</td>\n",
       "      <td>1.057607</td>\n",
       "      <td>1.110129</td>\n",
       "      <td>-0.311153</td>\n",
       "      <td>0.064482</td>\n",
       "      <td>0.111043</td>\n",
       "      <td>-0.820000</td>\n",
       "      <td>1.724138</td>\n",
       "      <td>14.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1728</th>\n",
       "      <td>985</td>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>252.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>404.0</td>\n",
       "      <td>371.0</td>\n",
       "      <td>387.0</td>\n",
       "      <td>353.0</td>\n",
       "      <td>326.0</td>\n",
       "      <td>845.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-39.0</td>\n",
       "      <td>0.644003</td>\n",
       "      <td>1.204234</td>\n",
       "      <td>0.882556</td>\n",
       "      <td>-0.416832</td>\n",
       "      <td>0.198578</td>\n",
       "      <td>-0.129387</td>\n",
       "      <td>-1.685185</td>\n",
       "      <td>-6.000000</td>\n",
       "      <td>-0.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1729</th>\n",
       "      <td>985</td>\n",
       "      <td>2017-08-01</td>\n",
       "      <td>342.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>404.0</td>\n",
       "      <td>371.0</td>\n",
       "      <td>387.0</td>\n",
       "      <td>353.0</td>\n",
       "      <td>326.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-81.0</td>\n",
       "      <td>1.325461</td>\n",
       "      <td>1.003182</td>\n",
       "      <td>0.810763</td>\n",
       "      <td>0.290397</td>\n",
       "      <td>0.002830</td>\n",
       "      <td>-0.207600</td>\n",
       "      <td>-1.512821</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>-1.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1730</th>\n",
       "      <td>985</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>388.0</td>\n",
       "      <td>342.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>404.0</td>\n",
       "      <td>371.0</td>\n",
       "      <td>387.0</td>\n",
       "      <td>353.0</td>\n",
       "      <td>...</td>\n",
       "      <td>198.0</td>\n",
       "      <td>0.869810</td>\n",
       "      <td>0.662536</td>\n",
       "      <td>1.739638</td>\n",
       "      <td>-0.133331</td>\n",
       "      <td>-0.521640</td>\n",
       "      <td>0.657208</td>\n",
       "      <td>-4.636364</td>\n",
       "      <td>0.068323</td>\n",
       "      <td>-4.351351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1731</th>\n",
       "      <td>985</td>\n",
       "      <td>2017-10-01</td>\n",
       "      <td>322.0</td>\n",
       "      <td>388.0</td>\n",
       "      <td>342.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>404.0</td>\n",
       "      <td>371.0</td>\n",
       "      <td>387.0</td>\n",
       "      <td>...</td>\n",
       "      <td>61.0</td>\n",
       "      <td>1.023236</td>\n",
       "      <td>0.992664</td>\n",
       "      <td>1.160122</td>\n",
       "      <td>0.025226</td>\n",
       "      <td>-0.008023</td>\n",
       "      <td>0.150950</td>\n",
       "      <td>1.405405</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-1.541667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1732</th>\n",
       "      <td>985</td>\n",
       "      <td>2017-11-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>322.0</td>\n",
       "      <td>388.0</td>\n",
       "      <td>342.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>404.0</td>\n",
       "      <td>371.0</td>\n",
       "      <td>...</td>\n",
       "      <td>130.0</td>\n",
       "      <td>1.036291</td>\n",
       "      <td>0.882480</td>\n",
       "      <td>1.472927</td>\n",
       "      <td>0.040376</td>\n",
       "      <td>-0.148161</td>\n",
       "      <td>0.404794</td>\n",
       "      <td>1.511628</td>\n",
       "      <td>0.544304</td>\n",
       "      <td>-1.549020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1733 rows × 507 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      brand_id  sale_date  sale_quantity  B_som_1  B_som_2  B_som_3  B_som_4  \\\n",
       "0           12 2012-01-01           47.0      NaN      NaN      NaN      NaN   \n",
       "1           12 2012-02-01           21.0     47.0      NaN      NaN      NaN   \n",
       "2           12 2012-03-01           10.0     21.0     47.0      NaN      NaN   \n",
       "3           12 2012-04-01           10.0     10.0     21.0     47.0      NaN   \n",
       "4           12 2012-05-01           10.0     10.0     10.0     21.0     47.0   \n",
       "5           12 2012-07-01           21.0      NaN     10.0     10.0     10.0   \n",
       "6           12 2012-10-01            5.0      NaN      NaN     21.0      NaN   \n",
       "7           12 2012-11-01           10.0      5.0      NaN      NaN     21.0   \n",
       "8           12 2012-12-01           16.0     10.0      5.0      NaN      NaN   \n",
       "9           12 2013-01-01           31.0     16.0     10.0      5.0      NaN   \n",
       "10          12 2013-03-01           26.0      NaN     31.0     16.0     10.0   \n",
       "11          12 2013-04-01           10.0     26.0      NaN     31.0     16.0   \n",
       "12          12 2013-05-01            5.0     10.0     26.0      NaN     31.0   \n",
       "13          12 2013-06-01            5.0      5.0     10.0     26.0      NaN   \n",
       "14          12 2013-09-01           16.0      NaN      NaN      5.0      5.0   \n",
       "15          12 2013-11-01            5.0      NaN     16.0      NaN      NaN   \n",
       "16          12 2014-01-01           16.0      NaN      5.0      NaN     16.0   \n",
       "17          12 2014-02-01           10.0     16.0      NaN      5.0      NaN   \n",
       "18          12 2014-08-01           18.0      NaN      NaN      NaN      NaN   \n",
       "19          12 2014-09-01          158.0     18.0      NaN      NaN      NaN   \n",
       "20          12 2014-10-01          437.0    158.0     18.0      NaN      NaN   \n",
       "21          12 2014-11-01          447.0    437.0    158.0     18.0      NaN   \n",
       "22          12 2014-12-01          455.0    447.0    437.0    158.0     18.0   \n",
       "23          12 2015-01-01          857.0    455.0    447.0    437.0    158.0   \n",
       "24          12 2015-02-01          846.0    857.0    455.0    447.0    437.0   \n",
       "25          12 2015-03-01          455.0    846.0    857.0    455.0    447.0   \n",
       "26          12 2015-04-01          296.0    455.0    846.0    857.0    455.0   \n",
       "27          12 2015-05-01          522.0    296.0    455.0    846.0    857.0   \n",
       "28          12 2015-06-01          419.0    522.0    296.0    455.0    846.0   \n",
       "29          12 2015-07-01          506.0    419.0    522.0    296.0    455.0   \n",
       "...        ...        ...            ...      ...      ...      ...      ...   \n",
       "1703       985 2015-06-01          322.0    542.0    512.0    408.0    413.0   \n",
       "1704       985 2015-07-01          370.0    322.0    542.0    512.0    408.0   \n",
       "1705       985 2015-08-01          323.0    370.0    322.0    542.0    512.0   \n",
       "1706       985 2015-09-01          467.0    323.0    370.0    322.0    542.0   \n",
       "1707       985 2015-10-01          469.0    467.0    323.0    370.0    322.0   \n",
       "1708       985 2015-11-01          425.0    469.0    467.0    323.0    370.0   \n",
       "1709       985 2015-12-01          469.0    425.0    469.0    467.0    323.0   \n",
       "1710       985 2016-01-01          553.0    469.0    425.0    469.0    467.0   \n",
       "1711       985 2016-02-01          248.0    553.0    469.0    425.0    469.0   \n",
       "1712       985 2016-03-01          319.0    248.0    553.0    469.0    425.0   \n",
       "1713       985 2016-04-01          535.0    319.0    248.0    553.0    469.0   \n",
       "1714       985 2016-05-01          341.0    535.0    319.0    248.0    553.0   \n",
       "1715       985 2016-06-01          281.0    341.0    535.0    319.0    248.0   \n",
       "1716       985 2016-07-01          279.0    281.0    341.0    535.0    319.0   \n",
       "1717       985 2016-08-01          382.0    279.0    281.0    341.0    535.0   \n",
       "1718       985 2016-09-01          416.0    382.0    279.0    281.0    341.0   \n",
       "1719       985 2016-10-01          521.0    416.0    382.0    279.0    281.0   \n",
       "1720       985 2016-11-01          490.0    521.0    416.0    382.0    279.0   \n",
       "1721       985 2016-12-01          845.0    490.0    521.0    416.0    382.0   \n",
       "1722       985 2017-01-01          326.0    845.0    490.0    521.0    416.0   \n",
       "1723       985 2017-02-01          353.0    326.0    845.0    490.0    521.0   \n",
       "1724       985 2017-03-01          387.0    353.0    326.0    845.0    490.0   \n",
       "1725       985 2017-04-01          371.0    387.0    353.0    326.0    845.0   \n",
       "1726       985 2017-05-01          404.0    371.0    387.0    353.0    326.0   \n",
       "1727       985 2017-06-01          273.0    404.0    371.0    387.0    353.0   \n",
       "1728       985 2017-07-01          252.0    273.0    404.0    371.0    387.0   \n",
       "1729       985 2017-08-01          342.0    252.0    273.0    404.0    371.0   \n",
       "1730       985 2017-09-01          388.0    342.0    252.0    273.0    404.0   \n",
       "1731       985 2017-10-01          322.0    388.0    342.0    252.0    273.0   \n",
       "1732       985 2017-11-01            NaN    322.0    388.0    342.0    252.0   \n",
       "\n",
       "      B_som_5  B_som_6  B_som_7    ...      B_sdy_3   B_sry_1   B_sry_2  \\\n",
       "0         NaN      NaN      NaN    ...          NaN       NaN       NaN   \n",
       "1         NaN      NaN      NaN    ...          NaN       NaN       NaN   \n",
       "2         NaN      NaN      NaN    ...          NaN       NaN       NaN   \n",
       "3         NaN      NaN      NaN    ...          NaN       NaN       NaN   \n",
       "4         NaN      NaN      NaN    ...          NaN       NaN       NaN   \n",
       "5        21.0     47.0      NaN    ...          NaN       NaN       NaN   \n",
       "6        10.0     10.0     10.0    ...          NaN       NaN       NaN   \n",
       "7         NaN     10.0     10.0    ...          NaN       NaN       NaN   \n",
       "8        21.0      NaN     10.0    ...          NaN       NaN       NaN   \n",
       "9         NaN     21.0      NaN    ...          NaN       NaN       NaN   \n",
       "10        5.0      NaN      NaN    ...          NaN       NaN       NaN   \n",
       "11       10.0      5.0      NaN    ...          NaN       NaN       NaN   \n",
       "12       16.0     10.0      5.0    ...          NaN       NaN       NaN   \n",
       "13       31.0     16.0     10.0    ...          NaN       NaN       NaN   \n",
       "14       10.0     26.0      NaN    ...          NaN       NaN       NaN   \n",
       "15        5.0      5.0     10.0    ...          NaN       NaN       NaN   \n",
       "16        NaN      NaN      5.0    ...          NaN       NaN       NaN   \n",
       "17       16.0      NaN      NaN    ...          NaN       NaN       NaN   \n",
       "18        NaN     10.0     16.0    ...          NaN       NaN       NaN   \n",
       "19        NaN      NaN     10.0    ...          NaN       NaN       NaN   \n",
       "20        NaN      NaN      NaN    ...          NaN       NaN       NaN   \n",
       "21        NaN      NaN      NaN    ...          NaN       NaN       NaN   \n",
       "22        NaN      NaN      NaN    ...          NaN       NaN       NaN   \n",
       "23       18.0      NaN      NaN    ...         47.0  0.782518       NaN   \n",
       "24      158.0     18.0      NaN    ...         21.0       NaN       NaN   \n",
       "25      437.0    158.0     18.0    ...         10.0       NaN       NaN   \n",
       "26      447.0    437.0    158.0    ...         10.0       NaN       NaN   \n",
       "27      455.0    447.0    437.0    ...         10.0       NaN       NaN   \n",
       "28      857.0    455.0    447.0    ...          NaN       NaN       NaN   \n",
       "29      846.0    857.0    455.0    ...         21.0       NaN       NaN   \n",
       "...       ...      ...      ...    ...          ...       ...       ...   \n",
       "1703    698.0    409.0    382.0    ...        241.0  1.110129       NaN   \n",
       "1704    413.0    698.0    409.0    ...        295.0  0.882556       NaN   \n",
       "1705    408.0    413.0    698.0    ...        371.0  0.810763       NaN   \n",
       "1706    512.0    408.0    413.0    ...        332.0  1.739638       NaN   \n",
       "1707    542.0    512.0    408.0    ...        419.0  1.160122       NaN   \n",
       "1708    322.0    542.0    512.0    ...        354.0  1.472927       NaN   \n",
       "1709    370.0    322.0    542.0    ...        313.0  0.708745       NaN   \n",
       "1710    323.0    370.0    322.0    ...       -473.0  1.105795  0.845293   \n",
       "1711    467.0    323.0    370.0    ...       -199.0  2.276140  0.476013   \n",
       "1712    469.0    467.0    323.0    ...       -352.0  3.312719  0.790449   \n",
       "1713    425.0    469.0    467.0    ...        -68.0  1.056298  0.861243   \n",
       "1714    469.0    425.0    469.0    ...         34.0  2.001847  0.364680   \n",
       "1715    553.0    469.0    425.0    ...       -239.0  1.057607  1.110129   \n",
       "1716    248.0    553.0    469.0    ...       -265.0  1.204234  0.882556   \n",
       "1717    319.0    248.0    553.0    ...       -335.0  1.003182  0.810763   \n",
       "1718    535.0    319.0    248.0    ...       -369.0  0.662536  1.739638   \n",
       "1719    341.0    535.0    319.0    ...       -443.0  0.992664  1.160122   \n",
       "1720    281.0    341.0    535.0    ...       -405.0  0.882480  1.472927   \n",
       "1721    279.0    281.0    341.0    ...       -201.0  1.191558  0.708745   \n",
       "1722    382.0    279.0    281.0    ...       -103.0  0.729836  1.105795   \n",
       "1723    416.0    382.0    279.0    ...       -265.0  0.405654  2.276140   \n",
       "1724    521.0    416.0    382.0    ...        -42.0  0.346856  3.312719   \n",
       "1725    490.0    521.0    416.0    ...         -1.0  0.724506  1.056298   \n",
       "1726    845.0    490.0    521.0    ...       -323.0  0.396992  2.001847   \n",
       "1727    326.0    845.0    490.0    ...         27.0  0.737163  1.057607   \n",
       "1728    353.0    326.0    845.0    ...        -39.0  0.644003  1.204234   \n",
       "1729    387.0    353.0    326.0    ...        -81.0  1.325461  1.003182   \n",
       "1730    371.0    387.0    353.0    ...        198.0  0.869810  0.662536   \n",
       "1731    404.0    371.0    387.0    ...         61.0  1.023236  0.992664   \n",
       "1732    273.0    404.0    371.0    ...        130.0  1.036291  0.882480   \n",
       "\n",
       "       B_sry_3  B_dfry_1  B_dfry_2  B_dfry_3   B_rfdy_1   B_rfdy_2   B_rfdy_3  \n",
       "0          NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "1          NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "2          NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "3          NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "4          NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "5          NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "6          NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "7          NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "8          NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "9          NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "10         NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "11         NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "12         NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "13         NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "14         NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "15         NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "16         NaN       NaN       NaN       NaN  -0.340426        NaN        NaN  \n",
       "17         NaN       NaN       NaN       NaN  -1.000000        NaN        NaN  \n",
       "18         NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "19         NaN       NaN       NaN       NaN        NaN        NaN        NaN  \n",
       "20         NaN       NaN       NaN       NaN  -1.000000        NaN        NaN  \n",
       "21         NaN       NaN       NaN       NaN  -0.500000        NaN        NaN  \n",
       "22         NaN       NaN       NaN       NaN  -1.000000        NaN        NaN  \n",
       "23         NaN -0.143445       NaN       NaN   0.937500  -0.340426        NaN  \n",
       "24         NaN       NaN       NaN       NaN  -0.476190  -1.000000        NaN  \n",
       "25         NaN -2.600000       NaN       NaN  -1.625000   1.600000        NaN  \n",
       "26         NaN -1.000000       NaN       NaN        NaN        NaN        NaN  \n",
       "27         NaN -0.500000       NaN       NaN   1.000000  -0.500000        NaN  \n",
       "28         NaN       NaN       NaN       NaN  -1.000000        NaN        NaN  \n",
       "29         NaN       NaN       NaN       NaN        NaN  -1.000000        NaN  \n",
       "...        ...       ...       ...       ...        ...        ...        ...  \n",
       "1703       NaN  0.111043       NaN       NaN  14.500000   0.008299        NaN  \n",
       "1704       NaN -0.129387       NaN       NaN  -0.300000   0.101695        NaN  \n",
       "1705       NaN -0.207600       NaN       NaN  -1.250000   0.097035        NaN  \n",
       "1706       NaN  0.657208       NaN       NaN  -4.351351  -0.111446        NaN  \n",
       "1707       NaN  0.150950       NaN       NaN  -1.541667  -0.057279        NaN  \n",
       "1708       NaN  0.404794       NaN       NaN  -1.549020  -0.144068        NaN  \n",
       "1709       NaN -0.395475       NaN       NaN  -0.142857   0.357827        NaN  \n",
       "1710       NaN  0.103857 -0.179668       NaN  -4.583333  -0.131868   0.161348  \n",
       "1711       NaN  0.829937 -0.715892       NaN  -0.893333  -1.304348   0.366242  \n",
       "1712       NaN  1.573693 -0.180390       NaN  -2.670588   1.976744  -0.139159  \n",
       "1713       NaN  0.076869 -0.219981       NaN   1.652632   0.989583   0.585366  \n",
       "1714       NaN  0.793129 -1.379188       NaN  -2.222222  -0.386266   1.170854  \n",
       "1715       NaN  0.064482  0.111043       NaN   1.724138  14.500000   0.008299  \n",
       "1716       NaN  0.198578 -0.129387       NaN  -6.000000  -0.300000   0.101695  \n",
       "1717       NaN  0.002830 -0.207600       NaN   0.866667  -1.250000   0.097035  \n",
       "1718       NaN -0.521640  0.657208       NaN   0.068323  -4.351351  -0.111446  \n",
       "1719       NaN -0.008023  0.150950       NaN   1.000000  -1.541667  -0.057279  \n",
       "1720       NaN -0.148161  0.404794       NaN   0.544304  -1.549020  -0.144068  \n",
       "1721       NaN  0.184346 -0.395475       NaN  -3.750000  -0.142857   0.357827  \n",
       "1722  0.845293 -0.293273  0.103857 -0.179668  -2.636364  -4.583333  -0.131868  \n",
       "1723  0.476013 -0.879802  0.829937 -0.715892  -1.231343  -0.893333  -1.304348  \n",
       "1724  0.790449 -1.472281  1.573693 -0.180390  -0.392070  -2.670588   1.976744  \n",
       "1725  0.861243 -0.397332  0.076869 -0.219981   0.146497   1.652632   0.989583  \n",
       "1726  0.364680 -0.955644  0.793129 -1.379188  -1.005000  -2.222222  -0.386266  \n",
       "1727  1.110129 -0.311153  0.064482  0.111043  -0.820000   1.724138  14.500000  \n",
       "1728  0.882556 -0.416832  0.198578 -0.129387  -1.685185  -6.000000  -0.300000  \n",
       "1729  0.810763  0.290397  0.002830 -0.207600  -1.512821   0.866667  -1.250000  \n",
       "1730  1.739638 -0.133331 -0.521640  0.657208  -4.636364   0.068323  -4.351351  \n",
       "1731  1.160122  0.025226 -0.008023  0.150950   1.405405   1.000000  -1.541667  \n",
       "1732  1.472927  0.040376 -0.148161  0.404794   1.511628   0.544304  -1.549020  \n",
       "\n",
       "[1733 rows x 507 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 存盘\n",
    "tmp1.to_csv(\"../../data/features/B_features.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分品牌统计不同时间的销量信息\n",
    "def calc_features_on_brand_and_time(df):\n",
    "    \"\"\"\n",
    "    Args:\n",
    "        df: 完整的数据集\n",
    "    Return:\n",
    "        tmp：基于综合时间和品牌信息，构造出的特征们\n",
    "    \"\"\"\n",
    "    g_brand_date = df.groupby(['brand_id','sale_date']).sum().reset_index()[['brand_id','sale_date','sale_quantity']]\n",
    "    gg = g_brand_date.groupby('brand_id')\n",
    "\n",
    "    # 过去三年内的每个月销量\n",
    "    tmp = g_brand_date\n",
    "    for i in range(37):\n",
    "        tmp['brand_sale_of_month_' + str(i+1) + '_ago'] = gg.apply(f, -(i+1), -i).reset_index()['sale_date']\n",
    "\n",
    "\n",
    "    # 该品牌过去2~36个月分别的销量和（更快的方式，应该是直接用上面求出的各个月销量的结果，直接求和！而不是这样重新分组算！）\n",
    "    tmp['brand_sum_sale_of_last_1_month'] = tmp['brand_sale_of_month_1_ago']\n",
    "    for i in range(36):\n",
    "        # tmp['sum_sale_of_last_' + str(i+1) + '_month'] = gg.apply(f, -(i+1), 0).reset_index()['sale_date']\n",
    "        tmp['brand_sum_sale_of_last_' + str(i+2) + '_month'] = tmp['brand_sum_sale_of_last_' + str(i+1) + '_month'] + \\\n",
    "                                                            tmp['brand_sale_of_month_' + str(i+2) + '_ago']\n",
    "    tmp = tmp.drop('brand_sum_sale_of_last_1_month', axis=1) # 再把这一列删掉，因为和前面的 sale_of_month_1_ago 列是重复的\n",
    "\n",
    "    # 该品牌往年这个月比上个月的销量比值\n",
    "    # 该品牌往年这个月减去上个月的销量差值\n",
    "    for i in range(3): # 只看过去三年的\n",
    "        thismonth = tmp['brand_sale_of_month_' + str((i+1)*12) + '_ago']\n",
    "        lastmonth = tmp['brand_sale_of_month_' + str((i+1)*12+1) + '_ago'] # gg.apply(f, -(i+1)*12-1, -(i+1)*12).reset_index()['sale_date']\n",
    "        tmp['brand_rate_of_this_month_divby_last_month_' + str(i+1) + '_year_ago'] = thismonth / lastmonth\n",
    "        tmp['brand_diff_of_this_month_sub_last_month_' + str(i+1) + '_year_ago'] = thismonth - lastmonth\n",
    "\n",
    "    # 该品牌上个月比上上个月的比值\n",
    "    thisyear_lastmonth = tmp['brand_sale_of_month_1_ago']\n",
    "    thisyear_lastlastmonth = tmp['brand_sale_of_month_2_ago']\n",
    "    tmp['brand_rate_of_last_divby_lastlast'] = thisyear_lastmonth / thisyear_lastlastmonth\n",
    "    # 该品牌上个月减去上上个月的差值\n",
    "    tmp['brand_diff_of_last_sub_lastlast'] = thisyear_lastmonth - thisyear_lastlastmonth\n",
    "\n",
    "    # 重命名一下销量列，明确含义，并避免后面join时冲突\n",
    "    tmp = tmp.rename(columns={'sale_quantity': 'brand_sale_quantity'})\n",
    "    # 注意要把np.inf替换为空值，在上面算月销量比例时，引入了inf，其实应该作为空值。\n",
    "    return tmp.replace([np.inf, -np.inf], np.nan)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
